unit DuplicatasDM;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, LResources, Forms, Controls, Graphics, Dialogs,
  ZDataset, ZSqlUpdate, ZSequence, MainDM, QueryConditions, DB;

type

  { TDuplicataData }

  TDuplicataData = class(TMainDataModule)
    DuplicataPagarDatasource: TDatasource;
    DuplicataPagarZQuerydata_pagamento: TDateField;
    DuplicataPagarZQuerydata_vencimento: TDateField;
    DuplicataPagarZQuerydesconto: TFloatField;
    DuplicataPagarZQueryid_conta: TLargeintField;
    DuplicataPagarZQueryid_duplicata_pagar: TLargeintField;
    DuplicataPagarZQueryid_entrada: TLargeintField;
    DuplicataPagarZQueryid_fornecedor: TLargeintField;
    DuplicataPagarZQueryid_plano_conta: TLargeintField;
    DuplicataPagarZQueryjuros: TFloatField;
    DuplicataPagarZQuerynome_pessoa: TStringField;
    DuplicataPagarZQuerynota: TStringField;
    DuplicataPagarZQueryparcela: TLargeintField;
    DuplicataPagarZQuerystatus: TStringField;
    DuplicataPagarZQueryvalor: TFloatField;
    DuplicataPagarZQueryvalor_pago: TFloatField;
    DuplicataPagarZSequence: TZSequence;
    DuplicataPagarZUpdate: TZUpdateSQL;
    DuplicataPagarZQuery: TZQuery;
    AuxiliarZQuery: TZQuery;
    DuplicataReceberDatasource: TDatasource;
    DuplicataReceberZQuery: TZQuery;
    DuplicataReceberZQueryacrescimo: TFloatField;
    DuplicataReceberZQuerydata_pagamento: TDateField;
    DuplicataReceberZQuerydata_vencimento: TDateField;
    DuplicataReceberZQuerydesconto: TFloatField;
    DuplicataReceberZQueryid_cliente: TLargeintField;
    DuplicataReceberZQueryid_conta: TLargeintField;
    DuplicataReceberZQueryid_duplicata_receber: TLargeintField;
    DuplicataReceberZQueryid_venda: TLargeintField;
    DuplicataReceberZQuerynome_pessoa: TStringField;
    DuplicataReceberZQuerynota: TStringField;
    DuplicataReceberZQueryparcela: TLargeintField;
    DuplicataReceberZQuerysigla_forma_pagamento: TStringField;
    DuplicataReceberZQuerystatus: TStringField;
    DuplicataReceberZQueryvalor: TFloatField;
    DuplicataReceberZQueryvalor_pago: TFloatField;
    procedure DataModuleCreate(Sender: TObject);
    /// Abre o DataSet
    procedure OpenDataSet(); override;
    /// Fecha o DataSet
    procedure CloseDataSet(); override;
    /// Realiza busca
    /// @param(Condicoes são as condições para a busca)
    /// @param(Tabela é a tabela no banco de dados)
    procedure Search(Condicoes: TQueryConditions; Tabela: string); override;
    function BuscaDuplicata(IdDuplicata, Parcela: integer): boolean;
    procedure InsereBaixaDuplicata(IDduplicata: string; Parcela: string; Status: string);
    procedure SalvaDuplicataPagarClonagem();
    function BuscaData(): string;
    procedure BuscaDuplicatasTodas();
    procedure BuscaDuplicatasAtrasadas();
    procedure BuscaDuplicatasQuitadas();
    procedure BuscaDuplicatasCanceladas();
    procedure BuscaDuplicatasAVencer();
    procedure BuscaDuplicataData(Data:string);
    procedure BuscarDuplicataFornecedor(Fornecedor:string);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  DuplicataData: TDuplicataData;

implementation

uses ConnectionDM;

{ TDuplicataData }


procedure TDuplicataData.DataModuleCreate(Sender: TObject);
begin
  MainDataSet := DuplicataPagarZQuery;
end;

procedure TDuplicataData.OpenDataSet;
begin
  ConnectionData.EmptyQuery(DuplicataPagarZQuery, 'duplicata_pagar');
end;

procedure TDuplicataData.CloseDataSet();
begin
  DuplicataPagarZQuery.Close();
end;

procedure TDuplicataData.Search(Condicoes: TQueryConditions; Tabela: string);
begin
  ConnectionData.OpenQueryConditions(DuplicataPagarZQuery, Tabela, Condicoes);
end;

function TDuplicataData.BuscaDuplicata(IdDuplicata, Parcela: integer): boolean;
begin
  with DuplicataPagarZQuery do begin
    Close();
    SQL.Clear();
    SQL.Add('SELECT p.nome_pessoa, dp.* FROM duplicata_pagar as dp, pessoa as p ');
    SQL.Add('WHERE dp.id_duplicata_pagar=:id_duplicata_pagar AND dp.parcela=:parcela AND dp.id_fornecedor = p.id_pessoa');
    ParamByName('id_duplicata_pagar').AsInteger := IdDuplicata;
    ParamByName('parcela').AsInteger := Parcela;
    Open();
    if RecordCount > 0 then begin
      Result := True;
    end else begin
      Result := False;
    end;
  end;
    with DuplicataReceberZQuery do begin
    Close();
    SQL.Clear();
    SQL.Add('SELECT p.nome_pessoa, dp.* FROM duplicata_receber as dp, pessoa as p ');
    SQL.Add('WHERE dp.id_duplicata_receber=:id_duplicata_receber AND dp.parcela=:parcela AND dp.id_cliente = p.id_pessoa');
    ParamByName('id_duplicata_receber').AsInteger := IdDuplicata;
    ParamByName('parcela').AsInteger := Parcela;
    Open();
    if RecordCount > 0 then begin
      Result := True;
    end else begin
      Result := False;
    end;
  end;
end;

procedure TDuplicataData.InsereBaixaDuplicata(IDduplicata: string;
  Parcela: string; Status: string);
begin
  with AuxiliarZQuery do begin
    Close();
    SQL.Clear();
    SQL.Add('UPDATE duplicata_pagar');
    SQL.Add('SET status =:status');
    SQL.Add('WHERE id_duplicata_pagar=:id_duplicata_pagar AND parcela =:parcela');
    ParamByName('id_duplicata_pagar').AsString := IDduplicata;
    ParamByName('parcela').AsString := Parcela;
    ParamByName('status').AsString := Status;
    ExecSQL();
  end;
end;


procedure TDuplicataData.SalvaDuplicataPagarClonagem();
begin
  DuplicataPagarZQuery.Insert();
end;

function TDuplicataData.BuscaData(): string;
var
  Data: string;
begin
  with AuxiliarZQuery do begin
    Close();
    SQL.Clear();
    SQL.Add('SELECT current_date as date');
    Open();
    Data := FieldByName('date').AsString;
    Result := Data;
  end;
end;

procedure TDuplicataData.BuscaDuplicatasTodas();
begin
  with DuplicataPagarZQuery do begin
    Close();
    SQL.Clear();
    SQL.Add('SELECT p.nome_pessoa, dp.* FROM duplicata_pagar as dp, pessoa as p, fornecedor as f');
    SQL.Add('WHERE dp.id_fornecedor=f.id_fornecedor and p.id_pessoa=f.id_fornecedor limit 30');
    Open();
  end;
   with DuplicataReceberZQuery do begin
    Close();
    SQL.Clear();
    SQL.Add('SELECT p.nome_pessoa, dr.* FROM duplicata_receber as dr, pessoa as p');
    SQL.Add('WHERE dr.id_cliente=p.id_pessoa');
    Open();
  end;
end;

procedure TDuplicataData.BuscaDuplicatasAtrasadas();
begin
   with DuplicataPagarZQuery do begin
    Close();
    SQL.Clear();
    SQL.Add('SELECT p.nome_pessoa, dp.* FROM duplicata_pagar as dp, pessoa as p, fornecedor as f');
    SQL.Add('WHERE dp.id_fornecedor=f.id_fornecedor and p.id_pessoa=f.id_fornecedor');
    SQL.Add('AND data_vencimento<current_date');
    SQL.Add('AND status <>:statusQ AND status <>:statusC limit 30');
    ParamByName('statusQ').AsString:='Q';
    ParamByName('statusC').AsString:='C';
    Open();
  end;
    with DuplicataReceberZQuery do begin
    Close();
    SQL.Clear();
    SQL.Add('SELECT p.nome_pessoa, dr.* FROM duplicata_receber as dr, pessoa as p, cliente as c');
    SQL.Add('WHERE dr.id_cliente=c.id_cliente and p.id_pessoa=c.id_cliente');
    SQL.Add('AND data_vencimento<current_date');
    SQL.Add('AND status <>:statusQ AND status <>:statusC');
    ParamByName('statusQ').AsString := 'Q';
    ParamByName('statusC').AsString := 'C';
    Open();
  end;
end;

procedure TDuplicataData.BuscaDuplicatasQuitadas();
begin
   with DuplicataPagarZQuery do begin
    Close();
    SQL.Clear();
    SQL.Add('SELECT p.nome_pessoa, dp.* FROM duplicata_pagar as dp, pessoa as p, fornecedor as f');
    SQL.Add('WHERE dp.id_fornecedor=f.id_fornecedor and p.id_pessoa=f.id_fornecedor');
    SQL.Add('AND status = :status limit 30 ');
    ParamByName('status').AsString:='Q';
    Open();
  end;
    with DuplicataReceberZQuery do begin
    Close();
    SQL.Clear();
    SQL.Add('SELECT p.nome_pessoa, dr.* FROM duplicata_receber as dr, pessoa as p, cliente as c');
    SQL.Add('WHERE dr.id_cliente=c.id_cliente and p.id_pessoa=c.id_cliente');
    SQL.Add('AND status = :status');
    ParamByName('status').AsString := 'Q';
    Open();
  end;
end;

procedure TDuplicataData.BuscaDuplicatasCanceladas();
begin
   with DuplicataPagarZQuery do begin
    Close();
    SQL.Clear();
    SQL.Add('SELECT p.nome_pessoa, dp.* FROM duplicata_pagar as dp, pessoa as p, fornecedor as f');
    SQL.Add('WHERE dp.id_fornecedor=f.id_fornecedor and p.id_pessoa=f.id_fornecedor');
    SQL.Add('AND status = :status limit 30 ');
    ParamByName('status').AsString:='C';
    Open();
  end;
   with DuplicataReceberZQuery do begin
    Close();
    SQL.Clear();
    SQL.Add('SELECT p.nome_pessoa, dr.* FROM duplicata_receber as dr, pessoa as p, cliente as c');
    SQL.Add('WHERE dr.id_cliente=c.id_cliente and p.id_pessoa=c.id_cliente');
    SQL.Add('AND status = :status');
    ParamByName('status').AsString := 'C';
    Open();
  end;
end;

procedure TDuplicataData.BuscaDuplicatasAVencer();
begin
   with DuplicataPagarZQuery do begin
    Close();
    SQL.Clear();
    SQL.Add('SELECT p.nome_pessoa, dp.* FROM duplicata_pagar as dp, pessoa as p, fornecedor as f');
    SQL.Add('WHERE dp.id_fornecedor=f.id_fornecedor and p.id_pessoa=f.id_fornecedor');
    SQL.Add('AND status <>:statusQ AND status <>:statusC limit 30');
    ParamByName('statusQ').AsString:='Q';
    ParamByName('statusC').AsString:='C';
    Open();
  end;
    with DuplicataReceberZQuery do begin
    Close();
    SQL.Clear();
    SQL.Add('SELECT p.nome_pessoa, dr.* FROM duplicata_receber as dr, pessoa as p, cliente as c');
    SQL.Add('WHERE dr.id_cliente=c.id_cliente and p.id_pessoa=c.id_cliente');
    SQL.Add('AND status <>:statusQ AND status <>:statusC');
    ParamByName('statusQ').AsString := 'Q';
    ParamByName('statusC').AsString := 'C';
    Open();
  end;
end;

procedure TDuplicataData.BuscaDuplicataData(Data: string);
begin
   with DuplicataPagarZQuery do begin
    Close();
    SQL.Clear();
    SQL.Add('SELECT p.nome_pessoa, dp.* FROM duplicata_pagar as dp, pessoa as p, fornecedor as f');
    SQL.Add('WHERE dp.id_fornecedor=f.id_fornecedor and p.id_pessoa=f.id_fornecedor');
    SQL.Add('AND data_vencimento=:data');
    ParamByName('data').AsString:=Data;
    Open();
  end;
    with DuplicataReceberZQuery do begin
    Close();
    SQL.Clear();
    SQL.Add('SELECT p.nome_pessoa, dr.* FROM duplicata_receber as dr, pessoa as p, cliente as c');
    SQL.Add('WHERE dr.id_cliente=c.id_cliente and p.id_pessoa=c.id_cliente');
    SQL.Add('AND data_vencimento=:data');
    ParamByName('data').AsString := Data;
    Open();
  end;
end;

procedure TDuplicataData.BuscarDuplicataFornecedor(Fornecedor: string);
begin
    with DuplicataPagarZQuery do begin
    Close();
    SQL.Clear();
    SQL.Add('SELECT p.nome_pessoa, dp.* FROM duplicata_pagar as dp, pessoa as p, fornecedor as f');
    SQL.Add('WHERE dp.id_fornecedor=f.id_fornecedor and p.id_pessoa=f.id_fornecedor');
    SQL.Add('AND p.nome_pessoa LIKE :nome');
    ParamByName('nome').AsString:='%'+Fornecedor+'%';
    Open();
  end;
     with DuplicataReceberZQuery do begin
    Close();
    SQL.Clear();
    SQL.Add('SELECT p.nome_pessoa, dr.* FROM duplicata_receber as dr, pessoa as p, cliente as c');
    SQL.Add('WHERE dr.id_cliente=c.id_cliente and p.id_pessoa=c.id_cliente');
    SQL.Add('AND p.nome_pessoa LIKE :nome');
    ParamByName('nome').AsString := '%' + Fornecedor + '%';
    Open();
  end;
end;

initialization
  {$I duplicatasdm.lrs}

end.
